<?php
namespace Manage\Controller;
use Think\Controller;

/**
 * Class ExportController
 * @package Manage\Controller
 * 导出
 */
class ExportController extends Controller {

    private $temp_file = './excelfile/汇总报表.xls';
    private $out_file_name = '汇总报表';

    public function print_pump(){
        header("Content-type: text/html; charset=utf-8");
        $start = '2016-06-01 00:00:00';
        $end = '2016-06-30 00:00:00';
        $pump_station = D('PumpingStation');
        dump($pump_station->count($start, $end));
        $csdt_model = D('Csdt');
        dump($csdt_model->count($start, $end));
        $gate_model = D("Gate");
        dump($gate_model->count($start, $end));
    }

    public function write_pumpsataion($sheet, $start_row,  $start, $end){
        $pump_station = D('PumpingStation');
        $list = $pump_station->count($start, $end);
        $row = $start_row;
        foreach($list as $v){
            $sheet->setCellValue('A'.$row,$v['date']);
            $sheet->setCellValue('B'.$row,$v['pump']);
            $sheet->setCellValue('C'.$row,$v['rain_time']);
            $sheet->setCellValue('D'.$row,$v['rain_flow']);
            $sheet->setCellValue('E'.$row,$v['sewage_time']);
            $sheet->setCellValue('F'.$row,$v['sewage_flow']);
            $sheet->setCellValue('G'.$row,$v['time']);
            $sheet->setCellValue('H'.$row,$v['flow']);
            $sheet->setCellValue('I'.$row,$v['mac_time']);
            $sheet->setCellValue('J'.$row,$v['ele']);
            $sheet->setCellValue('K'.$row,$v['water']);
            $row++;
        }
        return $row;
    }

    public function write_csdt($sheet, $start_row,  $start, $end){
        $csdt_model = D('Csdt');
        $list = $csdt_model->count($start, $end);
        $row = $start_row;
        foreach($list as $v){
            $sheet->setCellValue('A'.$row,$v['date']);
            $sheet->setCellValue('B'.$row,$v['pump']);
            $sheet->setCellValue('C'.$row,$v['index']);
            $sheet->setCellValue('D'.$row,$v['water_time']);
            $sheet->setCellValue('E'.$row,$v['time']);
            $sheet->setCellValue('F'.$row,$v['flow']);
            $sheet->setCellValue('G'.$row,$v['mac_time']);
            $sheet->setCellValue('H'.$row,$v['ele']);
            $sheet->setCellValue('I'.$row,$v['water']);
            $row++;
        }
        return $row;
    }

    public function write_gate($sheet, $start_row,  $start, $end){
        $gate_model = D("Gate");
        $list = $gate_model->count($start, $end);
        $row = $start_row;
        foreach($list as $v){
            $sheet->setCellValue('A'.$row,$v['date']);
            $sheet->setCellValue('B'.$row,$v['name']);
            $sheet->setCellValue('C'.$row,$v['number']);
            $sheet->setCellValue('D'.$row,$v['time']);
            $sheet->setCellValue('E'.$row,$v['res']);
            $row++;
        }
        return $row;
    }

    public function export_excel(){
        header("Content-type: text/html; charset=utf-8");
        import("Org.Util.PHPExcel");
        import("Org.Util.PHPExcel.Reader.Excel5");
        import("Org.Util.PHPExcel.Writer.Excel5");
        import("Org.Util.PHPExcel.IOFactory.php");
        $php_reader = new \PHPExcel_Reader_Excel5();
        $filename = file_zh_cn_icvon($this->temp_file);
        if(!$php_reader->canRead($filename)){
            echo '无法识别的Excel文件！';
            return false;
        }
        $start = I('get.start_date');
        $end = I('get.end_date');
        $start_row = 2;
        //载入文件
        $php_excel = $php_reader->load($filename);
        $currentSheet = $php_excel->getSheet(0);
        $php_excel->getActiveSheet()->setTitle('泵站运行记录表');
        $row = $this->write_pumpsataion($currentSheet, $start_row, $start, $end);
        $currentSheet->getStyle('A'.$start_row.':Z'.$row)->getFont()->setName('宋体');
        $currentSheet->getStyle('A'.$start_row.':Z'.$row)->getFont()->setSize(11);
        $currentSheet->getStyle('A'.$start_row.':Z'.$row)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $currentSheet->getStyle('A'.$start_row.':Z'.$row)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);

        $currentSheet = $php_excel->setActiveSheetIndex(1);
        $php_excel->getActiveSheet()->setTitle('调蓄池运行汇总表');
        $row = $this->write_csdt($currentSheet, $start_row, $start, $end);
        $currentSheet->getStyle('A'.$start_row.':Z'.$row)->getFont()->setName('宋体');
        $currentSheet->getStyle('A'.$start_row.':Z'.$row)->getFont()->setSize(11);
        $currentSheet->getStyle('A'.$start_row.':Z'.$row)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $currentSheet->getStyle('A'.$start_row.':Z'.$row)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);

        $currentSheet = $php_excel->setActiveSheetIndex(2);
        $php_excel->getActiveSheet()->setTitle('河道闸门运行记录表 ');
        $row = $this->write_gate($currentSheet, $start_row, $start, $end);
        $currentSheet->getStyle('A'.$start_row.':Z'.$row)->getFont()->setName('宋体');
        $currentSheet->getStyle('A'.$start_row.':Z'.$row)->getFont()->setSize(11);
        $currentSheet->getStyle('A'.$start_row.':Z'.$row)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $currentSheet->getStyle('A'.$start_row.':Z'.$row)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);

        $php_excel->setActiveSheetIndex(0);


//        $currentSheet->getStyle('A'.$this->start_row.':O'.$row)->getBorders()->getAllBorders()->setBorderStyle(\PHPExcel_Style_Border::BORDER_THIN);
//        $currentSheet->getStyle('A'.$this->start_row.':O'.$row)->getFont()->setName('宋体');
//        $currentSheet->getStyle('A'.$this->start_row.':O'.$row)->getFont()->setSize(11);
//        $currentSheet->getStyle('A'.$this->start_row.':O'.$row)->getFont()->setBold(true);
//        $currentSheet->getStyle('A'.$this->start_row.':O'.$row)->getFont()->getColor()->setARGB(\PHPExcel_Style_Color::COLOR_BLACK);
//        $currentSheet->getStyle('A'.$this->start_row.':O'.$row)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
//        $currentSheet->getStyle('A'.$this->start_row.':O'.$row)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);

        $php_writer = new \PHPExcel_Writer_Excel5($php_excel);

        ob_end_clean();//清除缓冲区,避免乱码
        header('Content-Type: application/vnd.ms-excel');
        $out = $start.'/'.$end.'_'.$this->out_file_name;
        header("Content-Disposition: attachment;filename=".$out.".xls");
        header('Cache-Control: max-age=0');
        $php_writer->save('php://output');
    }
}